package com.alvin.abp.service;

import java.beans.PropertyEditorSupport;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.springframework.beans.BeanWrapper;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.StatementCreatorUtils;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterUtils;
import org.springframework.jdbc.core.namedparam.ParsedSql;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;

import com.alvin.abp.common.context.PaginationContext;
import com.alvin.abp.common.util.AuditUtils;
import com.alvin.abp.domain.AbstractEntity;
/**
 * 
 * Basic SQL helper.
 * @author <a href="mailto:rex2.mj@gmail.com">Leo Meng</a>
 * @date $Date: Mar 25, 2013 $
 */
@Service(value = "serviceHelper")
public class ServiceHelper {

	public <T extends AbstractEntity> T update(
			NamedParameterJdbcTemplate jdbcTemplate, String sql, T entity) {
		if (entity.getId() == null) {
			throw new IllegalArgumentException();
		}
		sql = AuditUtils.appendUserstampSql(sql);
		AuditUtils.populateUserstamp(entity);

		jdbcTemplate.update(sql, createSqlParameterSource(entity));

		return entity;
	}

	public <T extends AbstractEntity> T insert(
			NamedParameterJdbcTemplate jdbcTemplate, String sql, T entity) {
		sql = AuditUtils.appendUserstampSql(sql);
		AuditUtils.populateUserstamp(entity);

		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(sql, createSqlParameterSource(entity), keyHolder);
		entity.setId((Long) keyHolder.getKey());
		return entity;
	}

	public SqlParameterSource createSqlParameterSource(Object o) {

		return new PetroBeanPropertySqlParameterSource(o);
	}

	public RowMapper<?> createRowMapper(Class<?> clazz) {
		return new AlvinBeanPropertyRowMapper(clazz);
	}

	@SuppressWarnings("unchecked")
	private final class AlvinBeanPropertyRowMapper extends BeanPropertyRowMapper {
		private AlvinBeanPropertyRowMapper(Class<?> mappedClass) {
			super(mappedClass);
		}
		
		@Override
		protected void initBeanWrapper(BeanWrapper bw) {
			Class[] entityTypes = { };
			for (Class clazz : entityTypes) {
				bw.registerCustomEditor(clazz, new EntityPropertyEditor(clazz));
			}

		}		

	}

	static class BatchUpdateConnectionCallback implements
			ConnectionCallback<Object> {
		private List<? extends AbstractEntity> entities;
		private String sql;

		private BatchUpdateConnectionCallback(
				List<? extends AbstractEntity> entitiesLocal, String sqlLocal) {
			this.entities = entitiesLocal;
			this.sql = sqlLocal;
		}

		@SuppressWarnings("unchecked")
		public Object doInConnection(Connection con) throws SQLException,
				DataAccessException {

			Iterator<? extends AbstractEntity> itr = entities.iterator();
			if (!itr.hasNext()) {
				return null;
			}

			AbstractEntity entity = itr.next();

			SqlParameterSource paramSource = new PetroBeanPropertySqlParameterSource(
					entity);
			ParsedSql parsedSql = NamedParameterUtils
					.parseSqlStatement(AuditUtils.appendUserstampSql(sql));
			String sqlToUse = NamedParameterUtils.substituteNamedParameters(
					parsedSql, paramSource);

			PreparedStatement stmt = con.prepareStatement(sqlToUse);

			for (AbstractEntity ent : entities) {
				paramSource = new PetroBeanPropertySqlParameterSource(ent);
				Object[] params = NamedParameterUtils.buildValueArray(
						parsedSql, paramSource, null);
				int[] paramTypes = NamedParameterUtils.buildSqlTypeArray(
						parsedSql, paramSource);
				List declaredParameters = SqlParameter
						.sqlTypesToAnonymousParameterList(paramTypes);
				AuditUtils.populateUserstamp(ent);

				setValues(stmt, Arrays.asList(params), declaredParameters);
				stmt.addBatch();
			}

			stmt.executeBatch();
			return null;
		}

		@SuppressWarnings("unchecked")
		private void setValues(PreparedStatement ps, List<Object> parameters,
				List declaredParameters) throws SQLException {
			// Determine PreparedStatement to pass to custom types.
			PreparedStatement psToUse = ps;

			// Set arguments: Does nothing if there are no parameters.
			int sqlColIndx = 1;
			for (int i = 0; i < parameters.size(); i++) {
				Object in = parameters.get(i);
				SqlParameter declaredParameter = (SqlParameter) declaredParameters
						.get(i);

				if (in instanceof Collection
						&& declaredParameter.getSqlType() != Types.ARRAY) {
					Collection entries = (Collection) in;
					for (Iterator it = entries.iterator(); it.hasNext();) {
						Object entry = it.next();
						if (entry instanceof Object[]) {
							Object[] valueArray = ((Object[]) entry);
							for (int k = 0; k < valueArray.length; k++) {
								Object argValue = valueArray[k];
								StatementCreatorUtils.setParameterValue(
										psToUse, sqlColIndx++,
										declaredParameter, argValue);
							}
						} else {
							StatementCreatorUtils.setParameterValue(psToUse,
									sqlColIndx++, declaredParameter, entry);
						}
					}
				} else {
					StatementCreatorUtils.setParameterValue(psToUse,
							sqlColIndx++, declaredParameter, in);
				}
			}
		}
	}

	static class PetroBeanPropertySqlParameterSource extends
			BeanPropertySqlParameterSource {

		public PetroBeanPropertySqlParameterSource(Object object) {
			super(object);
		}

		@Override
		public Object getValue(String paramName)
				throws IllegalArgumentException {
			Object value = super.getValue(paramName);

			if (value instanceof Enum<?>) {
				return ((Enum<?>) value).name();
			} else if (value instanceof AbstractEntity) {
				Long retval = ((AbstractEntity) value).getId();
				if (retval.equals(0L)) {
					retval = null;
				}
				return retval;
			}

			return value;
		}

	}

	static class EntityPropertyEditor extends PropertyEditorSupport {

		private Class<? extends AbstractEntity> clazz;

		public EntityPropertyEditor(Class<? extends AbstractEntity> clazz) {
			super();
			this.clazz = clazz;
		}

		@Override
		public void setValue(Object value) {

			if (value != null && !Long.valueOf(0).equals(value)) {
				try {
					AbstractEntity entity = clazz.newInstance();
					entity.setId(Long.parseLong(value.toString()));
					super.setValue(entity);
				} catch (Exception ex) {
					throw new IllegalArgumentException();
				}
			} else {
				super.setValue(value);
			}

		}

	}

	public String getCountSql(String sql) {

		String SELECT="SELECT";
		String FROM ="FROM";
		int fromLoc = -1;
		
		int selects=0;
		for(int i=0;i<sql.length();i++){
			char c=sql.charAt(i);
			if(Character.isSpaceChar(c) || c==','||c=='=' || c== '>' || c=='<' || c=='(' ||c==')'){
				//do nothing
			}else if (Character.isLetter(c)){
				int fromPos=i;
				StringBuffer buf=new StringBuffer();
				do{
					buf.append(c);
					i++;
				}while(i<sql.length() && Character.isLetter( c=sql.charAt(i) ));
				
				if(buf.toString().equalsIgnoreCase(SELECT)){
					selects++;
				}else if(buf.toString().equalsIgnoreCase(FROM)){
					selects--;
					if(selects == 0){
						fromLoc=fromPos;
						break;
					}
				}
			}
				
		}
		
		if(fromLoc==-1){
			throw new IllegalArgumentException("Can not generate count sql for ["+sql+"]");
		}
		
		Matcher orderMatcher = ORDER_PATTERN.matcher(sql);
		int orderLoc = orderMatcher.find() ? orderMatcher.start(1) : sql
				.length();

		return "select count(*) " + sql.substring(fromLoc, orderLoc);
	}
	

	@SuppressWarnings("unchecked")
	public List paginationQuery(NamedParameterJdbcTemplate jdbcTemplate,
			String sql, SqlParameterSource parameterSource, RowMapper rowMapper) {
		if (parameterSource == null) {
			parameterSource = new MapSqlParameterSource(EMPTY_MODEL);
		}
		String countSql = getCountSql(sql);
		String paginationSql = getPaginationSql(sql);

		PaginationContext ctx = PaginationContext.get();
		ctx.setCount(jdbcTemplate.queryForInt(countSql, parameterSource));

		List ret = jdbcTemplate
				.query(paginationSql, parameterSource, rowMapper);
		ctx.setPageCount(ret.size());
		return ret;
	}

	public void batchUpdate(NamedParameterJdbcTemplate jdbcTemplate,
			String sql, List<? extends AbstractEntity> entities) {
		final String sqlLocal = sql;
		final List<? extends AbstractEntity> entitiesLocal = entities;
		jdbcTemplate.getJdbcOperations().execute(
				new BatchUpdateConnectionCallback(entitiesLocal, sqlLocal));
	}

	public String getPaginationSql(String sql) {
		return sql + " LIMIT " + (PaginationContext.get().getCurrent() - 1)
				+ "," + PaginationContext.get().getChunk();
	}

	private static final String SQL_PATH_PATTERN = "com/petro/scheduler/resource/sql/${path}.SQL";
	private static final Map<String, Object> EMPTY_MODEL = new HashMap<String, Object>();

	@SuppressWarnings("unused")
	private static final Pattern FROM_PATTERN = Pattern.compile(
			"(^|\\s)(from)\\s", Pattern.CASE_INSENSITIVE);
	private static final Pattern ORDER_PATTERN = Pattern.compile(
			"\\s(order)(\\s)+by\\s", Pattern.CASE_INSENSITIVE);
	@SuppressWarnings("unused")
	private static final Pattern WHERE_PATTERN = Pattern.compile(
			"\\s(where)\\s", Pattern.CASE_INSENSITIVE);
	
	public static void main(String[] args) {

		// String sql="select id,fe,(select id from a) from b";

	}
}
